<?php
namespace app\controller;

use app\BaseController;
use think\facade\Db;
use think\Request;
use think\facade\View;
use think\helper\Str;
use think\exception\ValidateException;
use think\facade\Queue;


// 员工数据
class MembersTotal extends BaseController
{
    public function index()
    {
        return View::fetch();
    }


    public function getCount()
    {
        $moments = Db::name('members_study')->sum('moments');
        $learnnow = Db::name('classes_log')->sum('learning_time');
        $learning = Db::name('learning_log')->sum('learning_time');
        $live = Db::name('live_log')->sum('time');
        $integral = Db::name('integral')->sum('num');


        $data = [
            'moments' => str_replace(',', '', number_format((($moments)/3600), 2)),
            'learnnow' => str_replace(',', '', number_format((($learnnow)/3600), 2)),
            'learning' => str_replace(',', '', number_format((($learning)/3600), 2)),
            'live' => str_replace(',', '', number_format((($live)/3600), 2)),
            'integral' => $integral,
        ];

        return $this->outputAjax('获取成功', 0, $data);
    }

    public function getQxt()
    {
        $study_time = 0;
        for ($i = 0; $i <= 10000; $i+=100) {
            $count = 100;
            //+获取员工学习数据
            $string = "appId=".config('app.school.appid')."&authCorpId=".config('app.school.auth_corpid')."&count=".$count."&offset=".$i."&appSecret=".config('app.school.secret');
            $sign = strtoupper(md5($string));
            $url = "https://www.qingxuetang.com/open/api/corpData/getStatData";
            $arr = array(
                'appId'         => config('app.school.appid'),
                'authCorpId'    => config('app.school.auth_corpid'),
                'count'         => $count,
                'offset'        => $i,
                'sign'          => $sign
            );
            $res = json_decode(http_post($url, $arr),true);
            if ($res['ret'] == 0) {
                if (!empty($res['data'])) {
                    $info = $res['data']['dataList'];
                    if (empty($res['data']['dataList'])) {
                        break;
                    } else {
                        foreach ($info as &$val) {
                            $study_time += $val['studyTime'];
                        }

                    }
                }
            }
        }

        $data = [
            'qxt' => str_replace(',', '', number_format(($study_time/3600), 2)),
        ];

        return $this->outputAjax('获取成功', 0, $data);
    }



    public function getLive()
    {

        $total_time = 0;


        //毫秒级时间戳
        $timestamp = time() * 1000;

        $start = '2021-01-01';
        $end = date('Y-m-d', time());

        //签名
        $string = md5(config('app.zhibo.AppSecret').'appId'.config('app.zhibo.appId').'endDate'.$end.'startDate'.$start.'timestamp'.$timestamp.config('app.zhibo.AppSecret'));
        $sign = strtoupper($string);

        //请求url
        $url = 'http://api.polyv.net/live/v2/statistics/'.config('app.zhibo.userId').'/channel_summary?appId='.config('app.zhibo.appId').'&sign='.$sign.'&timestamp='.$timestamp.'&startDate='.$start.'&endDate='.$end;

        // 创建频道
        $res = zhibo_http_post($url, '', false, []);
        $res_data = json_decode($res, true);

        if ($res_data['code'] == 200) {
            foreach ($res_data['data'] as &$v) {
                $total_time += $v['pcPlayDuration'];
                $total_time += $v['mobilePlayDuration'];
            }
        }

        $data = [
            'live' => floor(($total_time/60)),
        ];

        return $this->outputAjax('获取成功', 0, $data);
    }


    public function load(Request $request)
    {

        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        $where = [];

        if (isset($get['tree_select_nodeId'])) {
            if (!empty($get['tree_select_nodeId'])) {
                $department = $get['tree_select_nodeId'];
                $arr = get_children($department, 1);
                $arr[] = $department;
                $department = implode('|', $arr);
                $where[]  = ['', 'EXP', Db::raw("CONCAT(',',a.department,',') REGEXP concat(',(',replace('".$department."',',','|'),'),')")];
            }
        }

        $time = [];
        $time1 = [];
        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $time[] = ['add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
                $time1[] = ['date', '>=', date('Ymd', strtotime($t[0]))];
                $time1[] = ['date', '<=', date('Ymd', strtotime($t[1]))];
                $start_date = $t[0];
                $end_date = $t[1];
            } else {
                $time[] = ['add_time', 'between', [strtotime(date('Y', time()).'-01-01'), time()]];
                $time1[] = ['date', '>=', date('Ymd', strtotime(date('Y', time()).'-01-01'))];
                $time1[] = ['date', '<=', date('Ymd', time())];
                $start_date = date('Y', time()).'-01-01';
                $end_date = date('Y-m-d', time());
            }
        } else {
            $time[] = ['add_time', 'between', [strtotime(date('Y', time()).'-01-01'), time()]];
            $time1[] = ['date', '>=', date('Ymd', strtotime(date('Y', time()).'-01-01'))];
            $time1[] = ['date', '<=', date('Ymd', time())];
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }


        if (isset($get['key'])) {
            if (!empty($get['key'])) {
                $where[] = ['a.name|a.userid', 'like', '%'.$get['key'].'%'];
            }
        }

        $where[] = ['a.status', '<>', 3];
        session('members_total_where', $where);


        $total = Db::table('o_members')
            ->alias('a')
            ->where($where)
            ->count();

        //+moments学习时间
        $subQuery1 = Db::table('o_study_log')
            ->where('type', '=', 1)
            ->where($time)
            ->field('SUM(time) as moments, userid')
            ->group('userid')
            ->buildSql();

        //+learnnow学习时长
        $subQuery2 = Db::table('o_classes_log')
            ->where($time)
            ->field('SUM(learning_time) as learnnow, userid')
            ->group('userid')
            ->buildSql();

        //+elearning学习时长
        $subQuery3 = Db::table('o_learning_log')
            ->where($time)
            ->field('SUM(learning_time) as learning, userid')
            ->group('userid')
            ->buildSql();

        //+直播课学习时长
        $subQuery4 = Db::table('o_live_log')
            ->where($time1)
            ->field('SUM(time) as live, userid')
            ->group('userid')
            ->buildSql();


        $rows = Db::table('o_members')
            ->alias('a')
            ->join($subQuery1.'b','a.userid=b.userid','left')
            ->join($subQuery2.'c','a.userid=c.userid','left')
            ->join($subQuery3.'d','a.userid=d.userid','left')
            ->join($subQuery4.'e','a.userid=e.userid','left')
            ->where($where)
            ->field('a.userid, a.openid, a.name, a.department_name, a.division, b.moments, c.learnnow, d.learning, e.live')
            ->order('id', 'asc')
            ->group('a.userid')
            ->page($get['page'], $get['limit'])
            ->select()
            ->toArray();

        foreach ($rows as &$v) {
            $v['qxt'] = 0;

            $qxt_time = get_stat_data_by_open_ids($v['userid'], $start_date, $end_date);
            if ($qxt_time['ret'] == 0) {
                if (!empty($qxt_time['data'])) {
                    $v['qxt'] = $qxt_time['data'][0]['studyTime'];
                }
            }

            $v['total'] = $v['qxt'] + $v['moments'] + $v['learnnow'] + $v['learning'] + $v['live'];
            $count = mb_strlen($v['userid']);
            if ($count == 6) {
                if (is_numeric($v['userid'])) {
                    $v['carolid'] = '00'.$v['userid'];
                } else {
                    $v['carolid'] = $v['userid'];
                }
            } else {
                $v['carolid'] = $v['userid'];
            }

            if (empty($v['moments'])) {
                $v['moments'] = 0;
            }
            if (empty($v['learnnow'])) {
                $v['learnnow'] = 0;
            }
            if (empty($v['learning'])) {
                $v['learning'] = 0;
            }
            if (empty($v['live'])) {
                $v['live'] = 0;
            }

        }
        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }

    // 导出
    public function export(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }
        $post = $request->post();

        $res = Db::name('document')
            ->where('type', '=', 'MembersTotalJobQueue')
            ->where('url', '=', '')
            ->find();

        if ($res) {
            return $this->outputAjax('已有下载任务, 请勿重复操作');
        }

        Db::startTrans();
        try {
            $where = [];

            if (!empty($post['tree_select_nodeId'])) {
                $department = $post['tree_select_nodeId'];
                $arr = get_children($department, 1);
                $arr[] = $department;
                $department = implode('|', $arr);
                $where[]  = ['', 'EXP', Db::raw("CONCAT(',',department,',') REGEXP concat(',(',replace('".$department."',',','|'),'),')")];
            }

            if (!empty($post['time'])) {
                $t = explode(' - ', $post['time']);
                $start = $t[0];
                $end = $t[1];
            } else {
                $start = date('Y', time()).'-01-01';
                $end = date('Y-m-d', time());
            }

            if (!empty($post['key'])) {
                $where[] = ['name|userid', 'like', '%'.$post['key'].'%'];
            }

            $where[] = ['status', '=', 1];


            $members = Db::name('members')
                ->where($where)
                ->field('id as members_id, name, userid, openid, department_name, division')
                ->select()
                ->toArray();

            foreach ($members as &$v) {
                $v['add_time'] = time();
                $v['type'] = 'MembersTotalJobQueue';
            }

            if (!Db::name('temporary_members')->insertAll($members)) {
                Db::rollback();
                return $this->outputAjax('任务添加失败[1]');
            }

            $ts = time();

            $insert = array(
                'admin_id' => session('admin_auth.id'),
                'title' => '员工学习明细-'.date('YmdHis', $ts),
                'url' => '',
                'type' => 'MembersTotalJobQueue',
                'add_time' => $ts
            );

            $download_id = Db::name('document')->insertGetId($insert);
            if (!$download_id) {
                Db::rollback();
                return $this->outputAjax('任务添加失败[2]');
            }

            // 1.当前任务由哪个类来负责处理
            // 当轮到该任务时，系统将生成该类的实例，并调用其fire方法
            $jobHandlerClassName = 'app\job\JobMembersTotal@task1';

            // 2.当任务归属的队列名称，如果为新队列，会自动创建
            $jobQueueName = "MembersTotalJobQueue";


            // 3.当前任务所需业务数据，不能为resource类型，其他类型最终将转化为json形式的字符串
            $jobData = ['ts' => $ts, 'download_id' => $download_id, 'page' => 1, 'start' => $start, 'end' => $end];

            // 4.将该任务推送到消息列表，等待对应的消费者去执行
            // 入队列，later延迟执行，单位秒，push立即执行
            $isPushedTask1 = Queue::push($jobHandlerClassName, $jobData, $jobQueueName);

            // database 驱动时，返回值为 1|false  ;   redis 驱动时，返回值为 随机字符串|false
            if ($isPushedTask1 !== false) {

                Db::commit();
                return $this->outputAjax('任务添加成功, 稍后在下载中心查看', 0);

            } else {

                Db::rollback();
                return $this->outputAjax('任务添加失败');

            }
        } catch (\Exception $e) {

            Db::rollback();
            return $this->outputAjax($e->getMessage());

        }
    }


    // 导入
    public function import()
    {
        return View::fetch();
    }


    // 执行导入
    public function executeImport(Request $request)
    {
        if (!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限', 2);
        }

        set_time_limit(0);

        $filename = $request->param('path');

        try {

            // 获取excel表格数据
            $result = get_excel_info($filename);
            if ($result['code'] == 1) {
                return $this->outputAjax($result['msg'], 2);
            }

            $i = 0;
            foreach ($result['data'] as $key => $value) {
                $where = [
                    'userid' => $value[0],
                    'type' => 2,
                    'date' => date('Ymd')
                ];
                $study = Db::name('study_log')->where($where)->find();

                if($study){
                    //修改时长
                    $res = Db::name('study_log')->where($where)->inc('time',$value[1])->update();
                }else{
                    //新增 要判断连续天数
                    $datas = [
                        'userid' => $value[0],
                        'type' => 2,
                        'time' => $value[1], //学习时长
                        'date' => date('Ymd'),
                        'duration' => 1, // 连续学习天数
                        'add_time' => time()
                    ];
                    //昨天数据条件
                    $day_where = [
                        'userid' => $value[0],
                        'date' => date('Ymd',(time() - 86400)) //前一天
                    ];
                    $yesterday = Db::name('study_log')->where($day_where)->find();//查询到一条就可以
                    $day = 1;
                    if($yesterday){
                        $day = $yesterday['duration'] + 1;
                    }
                    $datas['duration'] = $day;
                    $res = Db::name('study_log')->insert($datas);
                }
                if ($res) {
                    $i++;
                }
            }
            return $this->outputAjax('导入成功！', 0, [$i, count($result['data'])-$i]);
        } catch (\Exception $e) {
            return $this->outputAjax($e->getMessage(), 2);
        }
    }

    public function qxt(Request $request)
    {
        $userid = $request->param('userid');

        View::assign('userid',$userid);
        return View::fetch();
    }



    protected function getOffset($page, $limit)
    {
        return ($page*$limit) - $limit;
    }


    public function getQxtCourseList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();


        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $start_date = $t[0];
                $end_date = $t[1];
            } else {
                $start_date = date('Y', time()).'-01-01';
                $end_date = date('Y-m-d', time());
            }
        } else {
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }

        $offset = $this->getOffset($get['page'], $get['limit']);
        $string = "appId=".config('app.school.appid')."&authCorpId=".config('app.school.auth_corpid')."&count=".$get['limit']."&endDate=".$end_date."&offset=".$offset."&openId=".encrypts($get['userid'])."&startDate=".$start_date."&appSecret=".config('app.school.secret');
        $sign = strtoupper(md5($string));
        $url = "https://www.qingxuetang.com/open/api/corpData/getUserLearntCourses";
        $arr = array(
            'appId'         => config('app.school.appid'),
            'authCorpId'    => config('app.school.auth_corpid'),
            'startDate'    	=> $start_date,
            'endDate'   	=> $end_date,
            'count'         => $get['limit'],
            'offset'        => $offset,
            'openId'        => encrypts($get['userid']),
            'sign'          => $sign
        );
        $res = json_decode(http_post($url, $arr),true);
        if ($res['ret'] == 0) {
            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => $res['data']['totalCount'],
                'data'=> $res['data']['dataList']
            );
        } else {
            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => 0,
                'data'=> []
            );
        }

        return json($data);
    }


    // 轻学堂数据导出
    public function exportQxtCourse(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }
        $get = $request->get();

        if (!empty($get['time'])) {
            $t = explode(' - ', $get['time']);
            $start_date = $t[0];
            $end_date = $t[1];
        } else {
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }

        ob_end_clean ();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人轻学堂学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', '课程标题', '学习时长(S)', '学习进度(%)', '开始学习时间']);

        $count = mb_strlen($get['userid']);
        if ($count == 6) {
            if (is_numeric($get['userid'])) {
                $userid = "'00".$get['userid'];
            } else {
                $userid = $get['userid'];
            }
        } else {
            $userid = $get['userid'];
        }
        $name = Db::name('members')->where('userid', '=', $get['userid'])->value('name');
        $count = 100;
        for($i = 0; $i <= 1000; $i += 100) {
            $string = "appId=".config('app.school.appid')."&authCorpId=".config('app.school.auth_corpid')."&count=".$count."&endDate=".$end_date."&offset=".$i."&openId=".encrypts($get['userid'])."&startDate=".$start_date."&appSecret=".config('app.school.secret');
            $sign = strtoupper(md5($string));
            $url = "https://www.qingxuetang.com/open/api/corpData/getUserLearntCourses";
            $arr = array(
                'appId'         => config('app.school.appid'),
                'authCorpId'    => config('app.school.auth_corpid'),
                'startDate'    	=> $start_date,
                'endDate'   	=> $end_date,
                'count'         => $count,
                'offset'        => $i,
                'openId'        => encrypts($get['userid']),
                'sign'          => $sign
            );
            $res = json_decode(http_post($url, $arr),true);

            if (empty($res['data']['dataList'])) {
                break;
            } else {
                foreach ($res['data']['dataList'] as &$val) {

                    if (!empty($val['bookStartLearningTime'])) {
                        $date = substr($val['courseStartLearningTime'],0,10);
                        $courseStartLearningTime = date('Y-m-d H:i:s', $date);
                    } else {
                        $courseStartLearningTime = '';
                    }
                    $arr = [
                        $userid,
                        $name,
                        $val['courseTitle'],
                        $val['courseStudyTime'],
                        $val['progress'],
                        $courseStartLearningTime,
                    ];
                    fputcsv($fp, $arr);
                }
            }
            flush();
        }
    }

    public function book(Request $request)
    {
        $userid = $request->param('userid');

        View::assign('userid',$userid);
        return View::fetch();
    }


    public function getQxtBookList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();


        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $start_date = $t[0];
                $end_date = $t[1];
            } else {
                $start_date = date('Y', time()).'-01-01';
                $end_date = date('Y-m-d', time());
            }
        } else {
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }

        $offset = $this->getOffset($get['page'], $get['limit']);
        $string = "appId=".config('app.school.appid')."&authCorpId=".config('app.school.auth_corpid')."&count=".$get['limit']."&endDate=".$end_date."&offset=".$offset."&openId=".encrypts($get['userid'])."&startDate=".$start_date."&appSecret=".config('app.school.secret');
        $sign = strtoupper(md5($string));
        $url = "https://www.qingxuetang.com/open/api/corpData/getUserLearntBooks";
        $arr = array(
            'appId'         => config('app.school.appid'),
            'authCorpId'    => config('app.school.auth_corpid'),
            'startDate'    	=> $start_date,
            'endDate'   	=> $end_date,
            'count'         => $get['limit'],
            'offset'        => $offset,
            'openId'        => encrypts($get['userid']),
            'sign'          => $sign
        );
        $res = json_decode(http_post($url, $arr),true);

        if ($res['ret'] == 0) {
            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => $res['data']['totalCount'],
                'data'=> $res['data']['dataList']
            );
        } else {
            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => 0,
                'data'=> []
            );
        }

        return json($data);
    }


    // 轻学堂书籍数据导出
    public function exportQxtBook(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        if (!empty($get['time'])) {
            $t = explode(' - ', $get['time']);
            $start_date = $t[0];
            $end_date = $t[1];
        } else {
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }


        ob_end_clean ();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人轻学堂书籍学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', '书籍标题', '学习时长(S)', '学习进度(%)', '开始学习时间']);

        $count = mb_strlen($get['userid']);
        if ($count == 6) {
            if (is_numeric($get['userid'])) {
                $userid = "'00".$get['userid'];
            } else {
                $userid = $get['userid'];
            }
        } else {
            $userid = $get['userid'];
        }


        $name = Db::name('members')->where('userid', '=', $get['userid'])->value('name');
        $count = 100;
        for($i = 0; $i <= 1000; $i += 100) {
            $string = "appId=".config('app.school.appid')."&authCorpId=".config('app.school.auth_corpid')."&count=".$count."&endDate=".$end_date."&offset=".$i."&openId=".encrypts($get['userid'])."&startDate=".$start_date."&appSecret=".config('app.school.secret');
            $sign = strtoupper(md5($string));
            $url = "https://www.qingxuetang.com/open/api/corpData/getUserLearntBooks";
            $arr = array(
                'appId'         => config('app.school.appid'),
                'authCorpId'    => config('app.school.auth_corpid'),
                'startDate'    	=> $start_date,
                'endDate'   	=> $end_date,
                'count'         => $count,
                'offset'        => $i,
                'openId'        => encrypts($get['userid']),
                'sign'          => $sign
            );
            $res = json_decode(http_post($url, $arr),true);
            if (empty($res['data']['dataList'])) {
                break;
            } else {
                foreach ($res['data']['dataList'] as $key => $val) {
                    if (!empty($val['bookStartLearningTime'])) {
                        $date = substr($val['bookStartLearningTime'],0,10);
                        $bookStartLearningTime = date('Y-m-d H:i:s', $date);
                    } else {
                        $bookStartLearningTime = '';
                    }

                    $arr = [
                        $userid,
                        $name,
                        $val['bookTitle'],
                        $val['bookStudyTime'],
                        $val['progress'],
                        $bookStartLearningTime,
                    ];
                    fputcsv($fp, $arr);
                }
            }
            flush();
        }
    }



    public function learnnow(Request $request)
    {
        $userid = $request->param('userid');

        View::assign('userid',$userid);
        return View::fetch();
    }

    public function getLearnnowList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        $where = [];

        if (isset($get['keys'])) {
            if (!empty($get['keys'])) {
                $where[] = ['b.title|c.seminar', 'like', '%' . $get['keys'] . '%'];
            }
        }


        if (isset($get['status'])) {
            if (!empty($get['status'])) {
                $where[] = ['a.type', '=', $get['status']];
            }
        }

        $where[] = ['a.userid', '=', $get['userid']];

        //+学习时长
        $subQuery1 = Db::name('classes_log')
            ->where('userid', '=', $get['userid'])
            ->field('SUM(learning_time) as time, classes_id')
            ->group('classes_id')
            ->buildSql();

        $total = Db::table('o_classes_user')
            ->alias('a')
            ->join('o_classes b','b.id=a.classes_id','left')
            ->join('o_course c','c.courseid=b.courseid','left')
            ->join($subQuery1.'d','a.classes_id=d.classes_id','left')
            ->where($where)
            ->count();


        // 分页查询数据
        $rows = Db::table('o_classes_user')
            ->alias('a')
            ->join('o_classes b','b.id=a.classes_id','left')
            ->join('o_course c','c.courseid=b.courseid','left')
            ->join($subQuery1.'d','a.classes_id=d.classes_id','left')
            ->where($where)
            ->field('a.classes_id, a.userid, a.type, b.title, b.e_title, b.start, b.end, c.seminar, c.e_seminar, d.time')
            ->page($get['page'], $get['limit'])
            ->select()
            ->toArray();

        foreach ($rows as $k => $v) {

            $rows[$k]['class_title'] = $v['title'] == '' ? $v['e_title'] : $v['title'];
            $rows[$k]['seminar'] = $v['seminar'] == '' ? $v['e_seminar'] : $v['seminar'];

            if (empty($v['time'])) {
                $rows[$k]['time'] = 0;
            }

            if ($v['start'] > time()) {
                $rows[$k]['progress'] = '<span style="color: #009688;">未开始</span>';

            } elseif(($v['start'] <= time()) && ($v['end'] >= time())) {
                $rows[$k]['progress'] = '<span style="color: #1E9FFF;">进行中</span>';

            } elseif($v['end'] < time()) {
                $class_step_count = Db::name('classes_step_resource')->where('classes_id', '=', $v['classes_id'])->count();
                $class_log_count = Db::name('classes_log')
                    ->where('classes_id', '=', $v['classes_id'])
                    ->where('userid', '=', $v['userid'])
                    ->where('is_finish', '=', 1)
                    ->count();
                if ($class_step_count == $class_log_count) {
                    $rows[$k]['progress'] = '已完成';
                } else {
                    $rows[$k]['progress'] = '<span style="color: #FF5722;">已结束</span>';
                }
            }

            if ($v['type'] == 1) {
                $rows[$k]['reg_type_name'] = '后台导入';
            } else {
                $rows[$k]['reg_type_name'] = '现场注册';
            }

            $sign_time = Db::name('classes_log')
                ->where('classes_id', '=', $v['classes_id'])
                ->where('qiandao', '=', 1)
                ->where('userid', '=', $v['userid'])
                ->value('add_time');

            if (empty($sign_time)) {
                $rows[$k]['sign_time'] = '--------';
            } else {
                $rows[$k]['sign_time'] = date('Y-m-d H:i', $sign_time);
            }
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    // learnnow导出
    public function exportLearnnow(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        $where = [];

        if (!empty($get['keys'])) {
            $where[] = ['b.title|c.seminar', 'like', '%' . $get['keys'] . '%'];
        }


        if (!empty($get['status'])) {
            $where[] = ['a.type', '=', $get['status']];
        }

        $where[] = ['a.userid', '=', $get['userid']];


        ob_end_clean ();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人Learnnow学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', '课程', '班级', '学习时长(S)', '学习进度', '注册方式', '签到时间']);
        $name = Db::name('members')->where('userid', '=', $get['userid'])->value('name');
        //+学习时长
        $subQuery1 = Db::name('classes_log')
            ->where('userid', '=', $get['userid'])
            ->field('SUM(learning_time) as time, classes_id')
            ->group('classes_id')
            ->buildSql();

        $total = Db::table('o_classes_user')
            ->alias('a')
            ->join('o_classes b','b.id=a.classes_id','left')
            ->join('o_course c','c.courseid=b.courseid','left')
            ->join($subQuery1.'d','a.classes_id=d.classes_id','left')
            ->where($where)
            ->count();

        $nums = 100; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;
            $rows = Db::table('o_classes_user')
                ->alias('a')
                ->join('o_classes b','b.id=a.classes_id','left')
                ->join('o_course c','c.courseid=b.courseid','left')
                ->join($subQuery1.'d','a.classes_id=d.classes_id','left')
                ->where($where)
                ->field('a.classes_id, a.userid, a.type, b.title, b.e_title, b.start, b.end, c.seminar, c.e_seminar, d.time')
                ->limit($start, $nums)
                ->select()->toArray();

            foreach ($rows as $k => $v) {

                $class_title = $v['title'] == '' ? $v['e_title'] : $v['title'];
                $seminar = $v['seminar'] == '' ? $v['e_seminar'] : $v['seminar'];

                if (empty($v['time'])) {
                    $v['time'] = 0;
                }

                if ($v['start'] > time()) {
                    $progress = '未开始';
                }elseif(($v['start'] <= time()) && ($v['end'] >= time())) {
                    $progress = '进行中';
                }elseif($v['end'] < time()) {
                    $class_step_count = Db::name('classes_step_resource')->where('classes_id', '=', $v['classes_id'])->count();
                    $class_log_count = Db::name('classes_log')
                        ->where('classes_id', '=', $v['classes_id'])
                        ->where('userid', '=', $v['userid'])
                        ->where('is_finish', '=', 1)
                        ->count();
                    if ($class_step_count == $class_log_count) {
                        $progress = '已完成';
                    } else {
                        $progress = '已结束';
                    }
                }

                if ($v['type'] == 1) {
                    $reg_type_name = '后台导入';
                } else {
                    $reg_type_name = '现场注册';
                }

                $sign_time = Db::name('classes_log')
                    ->where('classes_id', '=', $v['classes_id'])
                    ->where('qiandao', '=', 1)
                    ->where('userid', '=', $v['userid'])
                    ->value('add_time');

                if (empty($sign_time)) {
                    $sign_time = '--------';
                } else {
                    $sign_time = date('Y-m-d H:i', $sign_time);
                }

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $userid = "'00".$v['userid'];
                    } else {
                        $userid = $v['userid'];
                    }
                } else {
                    $userid = $v['userid'];
                }

                $arr = [
                    $userid,
                    $name,
                    $seminar,
                    $class_title,
                    $v['time'],
                    $progress,
                    $reg_type_name,
                    $sign_time,
                ];
                fputcsv($fp, $arr);
            }
            //每1千条数据就刷新缓冲区
            flush();
        }
    }


    public function learnnowView(Request $request)
    {
        $userid = $request->get('userid');
        $class_id = $request->get('class_id');

        View::assign('userid', $userid);
        View::assign('class_id', $class_id);
        return View::fetch();
    }

    public function getLearnnowViewList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        $where = [];
        if (isset($get['type'])) {
            if (!empty($get['type'])) {
                $where[] = ['b.type', '=', $get['type']];
            }
        }

        if (isset($get['title'])) {
            if (!empty($get['title'])) {
                $where[] = ['a.title', 'like', '%' . $get['title'] . '%'];
            }
        }

        $where[] =['a.classes_id', '=', $get['class_id']];

        // 取得满足条件的记录数
        $total = Db::table('o_classes_step_resource')
            ->alias('a')
            ->where($where)
            ->count();

        // 分页查询数据
        $rows = Db::table('o_classes_step_resource')
            ->alias('a')
            ->join('o_courseware b','a.courseware_id=b.id','left')
            ->where($where)
            ->where('a.status', '=', 1)
            ->page($get['page'], $get['limit'])
            ->field('a.id, b.title, b.type, b.path')
            ->select()
            ->toArray();

        foreach ($rows as &$v) {
            if ($v['type'] == 1) {
                $v['type_name'] = '<span style="color: #1E9FFF;">图文</span>';
            } elseif ($v['type'] == 2) {
                $icon =  strrchr($v['path'],'.');
                if (in_array(strtolower($icon),['.mp4','.mov'])) {
                    $v['type_name'] = '<span style="color: #FFB800;">视频</span>';
                } elseif (strtolower($icon) == '.mp3') {
                    $v['type_name'] = '<span style="color: #FFB800;">音频</span>';
                } else {
                    $v['type_name'] = '<span style="color: #FFB800;">文档</span>';
                }
            } elseif ($v['type'] == 3) {
                $v['type_name'] = '<span style="color: #FF5722;">测试</span>';
            } elseif ($v['type'] == 4) {
                $v['type_name'] = '<span style="color: #FF5722;">Survey</span>';
            }

            $log = Db::name('classes_log')
                ->where('classes_id', '=', $get['class_id'])
                ->where('resource_id', '=', $v['id'])
                ->where('userid', '=', $get['userid'])
                ->field('add_time, is_finish, end_time, learning_time')
                ->find();
            if (empty($log)) {
                $v['start_time'] = '--------';
                $v['end_time'] = '--------';
                $v['learning_time'] = 0;
                $v['status_name'] = '未开始';
            } else {
                $v['start_time'] = date('Y-m-d H:i', $log['add_time']);
                $v['learning_time'] = $log['learning_time'];
                if ($log['is_finish'] == 0) {
                    $v['end_time'] = '--------';
                } else {
                    $v['end_time'] = date('Y-m-d H:i', $log['end_time']);
                }

                if ($log['is_finish'] == 0) {
                    $v['status_name'] = '学习中';
                } else {
                    $v['status_name'] = '已完成';
                }
            }

        }
        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    public function learning(Request $request)
    {
        $userid = $request->param('userid');

        View::assign('userid',$userid);
        return View::fetch();
    }


    public function getLearningList(Request $request)
    {

        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();
        $where = [];
        $where1 = [];

        if (isset($get['title'])) {
            if (!empty($get['title'])) {
                $where[] = ['b.title', 'like', '%' . $get['title'] . '%'];
            }
        }


        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where1[] = ['add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }

        $where[] = ['a.userid', '=', $get['userid']];


        //+学习时长
        $subQuery1 = Db::name('learning_log')
            ->where('userid', '=', $get['userid'])
            ->where($where1)
            ->field('SUM(learning_time) as time, learning_id')
            ->group('learning_id')
            ->buildSql();


        // 分页查询数据
        $rows = Db::table('o_learning_user')
            ->alias('a')
            ->join('o_learning b','b.id=a.learning_id','left')
            ->join($subQuery1.'d','a.learning_id=d.learning_id','left')
            ->where($where)
            ->field('a.learning_id, a.userid, b.title, d.time')
            ->select()
            ->toArray();

        foreach ($rows as $k => $v) {

            if (empty($v['time'])) {
                $rows[$k]['time'] = 0;
            }

            $learning_resource_count = Db::name('learning_step_resource')
                ->alias('a')
                ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
                ->where('a.learning_id', '=', $v['learning_id'])
                ->where('a.status', '=', 1)
                ->where('b.type', '<>', 4)
                ->where('b.type', '<>', 5)
                ->count();

            $survey = Db::name('learning_step_resource')
                ->alias('a')
                ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
                ->where('a.learning_id', '=', $v['learning_id'])
                ->where('a.status', '=', 1)
                ->where('b.type', '=', 4)
                ->field('a.id, a.courseware_id')
                ->select()
                ->toArray();

            $guanlian = [];
            $buguanlian = [];
            foreach ($survey as &$val) {
                $s = Db::name('courseware_survey')
                    ->where('courseware_id', '=', $val['courseware_id'])
                    ->find();
                if ($s['relation'] == 1) {
                    array_push($guanlian, $val['id']);
                } else {
                    array_push($buguanlian, $val['id']);
                }
            }
            $learning_log_count = Db::name('learning_log')
                ->where('learning_id', '=', $v['learning_id'])
                ->where('userid', '=', $v['userid'])
                ->where('is_finish', '=', 1)
                ->order('id', 'desc')
                ->group('resource_id')
                ->whereNotIn('resource_id', $buguanlian)
                ->count();

            if (($learning_resource_count + count($guanlian)) == $learning_log_count) {
                $rows[$k]['progress'] = '已完成';
            } else {
                $rows[$k]['progress'] = '<span style="color: #FF5722;">学习中</span>';
            }

            $start_time = Db::name('learning_log')
                ->where('learning_id', '=', $v['learning_id'])
                ->where('userid', '=', $v['userid'])
                ->order('id', 'asc')
                ->value('add_time');
            if (empty($start_time)) {
                $rows[$k]['start_time'] = '--------';
            } else {
                $rows[$k]['start_time'] = date('Y-m-d H:i', $start_time);
            }

            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                if ($start_time >= strtotime($t[0]) && $start_time <= strtotime($t[1])+86399) {
                } else {
                    unset($rows[$k]);
                }
            }
        }

        if (!empty($rows)) {
            //+分页
            $pages = $get['page'];
            $limit = $get['limit'];
            if (empty($pages) || $pages == 1) {
                $Pages = 0;
            } else {
                $Pages = ($pages-1)*$limit;
            }

            $datas = array_slice($rows, $Pages, $limit);

            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => count($rows),
                'data'=> $datas
            );
        } else {
            $data = array(
                'code'=> 0,
                'msg'=> '',
                'count' => 0,
                'data'=> []
            );
        }
        return json($data);
    }



    // elearning导出
    public function exportLearning(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        $where = [];
        $where1 = [];
        if (isset($get['title'])) {
            if (!empty($get['title'])) {
                $where[] = ['b.title', 'like', '%' . $get['title'] . '%'];
            }
        }


        $where[] = ['a.userid', '=', $get['userid']];

        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $where1[] = ['add_time', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
            }
        }


        ob_end_clean ();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人E-learning学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', '课程课程', '学习时长(S)', '学习进度', '开始学习时间']);
        $name = Db::name('members')->where('userid', '=', $get['userid'])->value('name');

        //+学习时长
        $subQuery1 = Db::name('learning_log')
            ->where('userid', '=', $get['userid'])
            ->where($where1)
            ->field('SUM(learning_time) as time, learning_id')
            ->group('learning_id')
            ->buildSql();


        $rows = Db::table('o_learning_user')
            ->alias('a')
            ->join('o_learning b','b.id=a.learning_id','left')
            ->join($subQuery1.'d','a.learning_id=d.learning_id','left')
            ->where($where)
            ->field('a.learning_id, a.userid, b.title, d.time')
            ->select()
            ->toArray();

        foreach ($rows as $k => $v) {

            if (empty($v['time'])) {
                $rows[$k]['time'] = 0;
            }

            $learning_resource_count = Db::name('learning_step_resource')
                ->alias('a')
                ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
                ->where('a.learning_id', '=', $v['learning_id'])
                ->where('a.status', '=', 1)
                ->where('b.type', '<>', 4)
                ->where('b.type', '<>', 5)
                ->count();

            $survey = Db::name('learning_step_resource')
                ->alias('a')
                ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
                ->where('a.learning_id', '=', $v['learning_id'])
                ->where('a.status', '=', 1)
                ->where('b.type', '=', 4)
                ->field('a.id, a.courseware_id')
                ->select()
                ->toArray();

            $guanlian = [];
            $buguanlian = [];
            foreach ($survey as &$val) {
                $s = Db::name('courseware_survey')
                    ->where('courseware_id', '=', $val['courseware_id'])
                    ->find();
                if ($s['relation'] == 1) {
                    array_push($guanlian, $val['id']);
                } else {
                    array_push($buguanlian, $val['id']);
                }
            }
            $learning_log_count = Db::name('learning_log')
                ->where('learning_id', '=', $v['learning_id'])
                ->where('userid', '=', $v['userid'])
                ->where('is_finish', '=', 1)
                ->order('id', 'desc')
                ->group('resource_id')
                ->whereNotIn('resource_id', $buguanlian)
                ->count();

            if (($learning_resource_count + count($guanlian)) == $learning_log_count) {
                $rows[$k]['progress'] = '已完成';
            } else {
                $rows[$k]['progress'] = '学习中';
            }

            $start_time = Db::name('learning_log')
                ->where('learning_id', '=', $v['learning_id'])
                ->where('userid', '=', $v['userid'])
                ->order('id', 'asc')
                ->value('add_time');
            if (empty($start_time)) {
                $rows[$k]['start_time'] = '--------';
            } else {
                $rows[$k]['start_time'] = date('Y-m-d H:i', $start_time);
            }

            $count = mb_strlen($v['userid']);
            if ($count == 6) {
                if (is_numeric($v['userid'])) {
                    $rows[$k]['userid'] = "'00".$v['userid'];
                }
            }

            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                if ($start_time >= strtotime($t[0]) && $start_time <= strtotime($t[1])+86399) {
                } else {
                    unset($rows[$k]);
                }
            }
        }
        $total = count($rows);

        $nums = 100; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 1; $i <= $step; $i++) {
            //+分页
            $limit = $nums;
            $pages = $i;
            if (empty($pages) || $pages == 1) {
                $Pages = 0;
            } else {
                $Pages = ($pages-1)*$limit;
            }

            $datas = array_slice($rows, $Pages, $limit);

            foreach ($datas as &$v) {
                $arr = [
                    $v['userid'],
                    $name,
                    $v['title'],
                    $v['time'],
                    $v['progress'],
                    $v['start_time']
                ];
                fputcsv($fp, $arr);
            }
            //每1千条数据就刷新缓冲区
            flush();
        }
    }


    public function learningView(Request $request)
    {
        $userid = $request->param('userid');
        $learning_id = $request->param('learning_id');

        View::assign('userid', $userid);
        View::assign('learning_id', $learning_id);
        return View::fetch();
    }

    public function getLearningViewList(Request $request)
    {

        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();

        $where = [];
        if (isset($get['type'])) {
            if (!empty($get['type'])) {
                $where[] = ['a.type', '=', $get['type']];
            }
        }

        if (isset($get['title'])) {
            if (!empty($get['title'])) {
                $where[] = ['a.title', 'like', '%' . $get['title'] . '%'];
            }
        }

        $where[] = ['a.learning_id', '=', $get['learning_id']];
        $where[] = ['a.status', '=', 1];

        // 取得满足条件的记录数
        $total = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
            ->where($where)
            ->count();

        // 分页查询数据
        $rows = Db::name('learning_step_resource')
            ->alias('a')
            ->join('o_courseware b', 'b.id=a.courseware_id', 'left')
            ->where($where)
            ->where('b.type', '<>', 5)
            ->page($get['page'], $get['limit'])
            ->field('a.id, b.title, b.type, b.path')
            ->select()
            ->toArray();

        foreach ($rows as &$v) {
            if ($v['type'] == 1) {
                $v['type_name'] = '<span style="color: #1E9FFF;">图文</span>';
            } elseif ($v['type'] == 2) {
                $icon =  strrchr($v['path'],'.');
                if (in_array(strtolower($icon),['.mp4','.mov'])) {
                    $v['type_name'] = '<span style="color: #FFB800;">视频</span>';
                } elseif (strtolower($icon) == '.mp3') {
                    $v['type_name'] = '<span style="color: #FFB800;">音频</span>';
                } else {
                    $v['type_name'] = '<span style="color: #FFB800;">文档</span>';
                }
            } elseif ($v['type'] == 3) {
                $v['type_name'] = '<span style="color: #FF5722;">测试</span>';
            } elseif ($v['type'] == 4) {
                $v['type_name'] = '<span style="color: #FF5722;">Survey</span>';
            }

            $log = Db::name('learning_log')
                ->where('learning_id', '=', $get['learning_id'])
                ->where('resource_id', '=', $v['id'])
                ->where('userid', '=', $get['userid'])
                ->field('add_time, is_finish, end_time, learning_time')
                ->order('id', 'desc')
                ->find();
            $learning_time = Db::name('learning_log')
                ->where('learning_id', '=', $get['learning_id'])
                ->where('resource_id', '=', $v['id'])
                ->where('userid', '=', $get['userid'])
                ->sum('learning_time');

            if (empty($log)) {
                $v['start_time'] = '--------';
                $v['end_time'] = '--------';
                $v['learning_time'] = 0;
                $v['status_name'] = '未开始';
            } else {
                $v['start_time'] = date('Y-m-d H:i', $log['add_time']);
                $v['learning_time'] = $learning_time;
                if ($log['is_finish'] == 0) {
                    $v['end_time'] = '--------';
                } else {
                    $v['end_time'] = date('Y-m-d H:i', $log['end_time']);
                }

                if ($log['is_finish'] == 0) {
                    $v['status_name'] = '学习中';
                } else {
                    $v['status_name'] = '已完成';
                }
            }

        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }



    // 直播课学习记录
    public function live(Request $request)
    {
        $userid = $request->param('userid');

        View::assign('userid',$userid);
        return View::fetch();
    }

    public function getLiveList(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }

        $get = $request->get();


        if (isset($get['time'])) {
            if (!empty($get['time'])) {
                $t = explode(' - ', $get['time']);
                $start_date = $t[0];
                $end_date = $t[1];
            } else {
                $start_date = date('Y', time()).'-01-01';
                $end_date = date('Y-m-d', time());
            }
        } else {
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }


        $openid = Db::name('members')->where('userid', '=', $get['userid'])->value('openid');


        $total = Db::table('o_live_user')
            ->alias('a')
            ->join('o_live b', 'b.channelId=a.channelId')
            ->where('a.userid', '=', $get['userid'])
            ->where('b.status', '=', 1)
            ->where('b.zb_status', '>', 1)
            ->count();


        $rows = Db::table('o_live_user')
            ->alias('a')
            ->join('o_live b', 'b.channelId=a.channelId')
            ->where('a.userid', '=', $get['userid'])
            ->where('b.status', '=', 1)
            ->where('b.zb_status', '>', 1)
            ->field('b.channelId, b.title')
            ->page($get['page'], $get['limit'])
            ->select()
            ->toArray();

        $date_arr = get_months($start_date, $end_date);

        foreach ($rows as &$value) {
            $live_total_time = 0;
            $start_study_date = 0;
            foreach ($date_arr as $key => $da) {
                $ts = explode(' ~ ', $da);
                //毫秒级时间戳
                $timestamp = time() * 1000;

                $start = strtotime($ts[0]) * 1000;
                $end = (strtotime($ts[1]) + 86399) * 1000;

                //签名
                $string = md5(config('app.zhibo.AppSecret').'appId'.config('app.zhibo.appId').'endTime'.$end.'param1'.$openid.'startTime'.$start.'timestamp'.$timestamp.config('app.zhibo.AppSecret'));
                $sign = strtoupper($string);

                //请求url
                $url = 'http://api.polyv.net/live/v2/statistics/'.$value['channelId'].'/viewlog?appId='.config('app.zhibo.appId').'&sign='.$sign.'&timestamp='.$timestamp.'&param1='.$openid.'&startTime='.$start.'&endTime='.$end;

                $res = zhibo_http_get($url);

                $res_data = json_decode($res, true);
                if ($res_data['code'] == 200) {
                    $content = $res_data['data']['contents'];

                    if(!empty($content)) {
                        $count = count($content);
                        foreach($content as $key => $cont) {
                            $live_total_time += $cont['playDuration'];
                            if ($count == ($key+1)) {
                                $start_study_date = $cont['createdTime'];
                            }
                        }
                    }
                }
            }

            $value['duration'] = $live_total_time;
            $value['start_study_date'] = $start_study_date;
        }

        $data = array(
            'code'=> 0,
            'msg'=> '',
            'count' => $total,
            'data'=> $rows
        );
        return json($data);
    }


    public function exportLive(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }

        $get = $request->get();

        if (!empty($get['time'])) {
            $t = explode(' - ', $get['time']);
            $start_date = $t[0];
            $end_date = $t[1];
        } else {
            $start_date = date('Y', time()).'-01-01';
            $end_date = date('Y-m-d', time());
        }

        ob_end_clean ();
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '员工个人直播课学习数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['工号', '姓名', '课程标题', '学习时长(S)', '开始学习时间']);


        $count = mb_strlen($get['userid']);
        if ($count == 6) {
            if (is_numeric($get['userid'])) {
                $userid = "'00".$get['userid'];
            } else {
                $userid = $get['userid'];
            }
        } else {
            $userid = $get['userid'];
        }

        $user = Db::name('members')->where('userid', '=', $get['userid'])->field('name, openid')->find();

        $total = Db::table('o_live_user')
            ->alias('a')
            ->join('o_live b', 'b.channelId=a.channelId')
            ->where('a.userid', '=', $get['userid'])
            ->where('b.status', '=', 1)
            ->where('b.zb_status', '>', 1)
            ->count();

        $nums = 100; //每次导出数量
        $step = ceil($total/$nums); //循环次数

        for($i = 0; $i < $step; $i++) {
            $start = $i * $nums;
            $rows = Db::table('o_live_user')
                ->alias('a')
                ->join('o_live b', 'b.channelId=a.channelId')
                ->where('a.userid', '=', $get['userid'])
                ->where('b.status', '=', 1)
                ->where('b.zb_status', '>', 1)
                ->field('b.channelId, b.title')
                ->limit($start, $nums)
                ->select()
                ->toArray();

            $date_arr = get_months($start_date, $end_date);

            foreach ($rows as &$value) {
                $live_total_time = 0;
                $start_study_date = 0;
                foreach ($date_arr as $key => $da) {
                    $ts = explode(' ~ ', $da);
                    //毫秒级时间戳
                    $timestamp = time() * 1000;

                    $start = strtotime($ts[0]) * 1000;
                    $end = (strtotime($ts[1]) + 86399) * 1000;

                    //签名
                    $string = md5(config('app.zhibo.AppSecret').'appId'.config('app.zhibo.appId').'endTime'.$end.'param1'.$user['openid'].'startTime'.$start.'timestamp'.$timestamp.config('app.zhibo.AppSecret'));
                    $sign = strtoupper($string);

                    //请求url
                    $url = 'http://api.polyv.net/live/v2/statistics/'.$value['channelId'].'/viewlog?appId='.config('app.zhibo.appId').'&sign='.$sign.'&timestamp='.$timestamp.'&param1='.$user['openid'].'&startTime='.$start.'&endTime='.$end;

                    $res = zhibo_http_get($url);

                    $res_data = json_decode($res, true);
                    if ($res_data['code'] == 200) {
                        $content = $res_data['data']['contents'];

                        if(!empty($content)) {
                            foreach($content as $key => $cont) {
                                $live_total_time += $cont['playDuration'];
                                if ($count == ($key+1)) {
                                    $start_study_date = $cont['createdTime'];
                                }
                            }
                        }
                    }
                }

                $arr = [
                    $userid,
                    $user['name'],
                    $value['title'],
                    $live_total_time,
                    date('Y-m-d H:i:s', substr($start_study_date, 0, 10))
                ];
                fputcsv($fp, $arr);
            }
            flush();
        }
    }
}